/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/

SET NOCOUNT ON

USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'MixNP') 
BEGIN
	ALTER DATABASE [MixNP] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE [MixNP]
	PRINT 'MixNP database was dropped';
END;
GO

CREATE DATABASE MixNP;
GO
USE MixNP;
GO
CREATE FUNCTION dbo.GetAlias
(
	@In character varying(128)
)
RETURNS character varying(128)
AS
BEGIN
RETURN
	REPLACE(	
	REPLACE(
	REPLACE(
	REPLACE(
	REPLACE(
	REPLACE(
	REPLACE(LOWER(LTRIM(RTRIM(@In))), ' ', '-'), '&','and'),'(',''),')',''),',',''),'/','-'),'''','')
END;
GO


CREATE TABLE dbo.Cities
(
	CityId			smallint NOT NULL IDENTITY CONSTRAINT Cities_PK PRIMARY KEY,
	CityName		national character varying(128) NOT NULL CONSTRAINT Cities_CityName_UNQ UNIQUE
);

GO

CREATE FUNCTION dbo.GetCityId
(
	@CityName national character varying(128)
)
RETURNS smallint
AS
BEGIN
	RETURN
	(
		SELECT CityId
		FROM dbo.Cities
		WHERE CityName=@CityName
	);
END;

GO


CREATE TABLE dbo.Categories
(
	CategoryId				integer NOT NULL IDENTITY CONSTRAINT Categories_PK PRIMARY KEY,
	CategoryName			national character varying(128) NOT NULL,
	CategoryDescription		national character varying(500) NOT NULL CONSTRAINT Categories_CategoryDescription_DF DEFAULT(''),
	Alias					national character varying(128),
	AllowEntry				bit NOT NULL,
	IsWarrantyApplicable	bit NOT NULL CONSTRAINT Categories_IsWarrantyApplicable_DF DEFAULT(0),
	IsUsed					bit NOT NULL CONSTRAINT Categories_IsUsed_DF DEFAULT(0),
	ParentCategoryId		integer NULL CONSTRAINT Categories_Categories_FK FOREIGN KEY REFERENCES dbo.Categories(CategoryId)
);

GO

CREATE TRIGGER UpdateAliasTrigger
ON dbo.Categories
INSTEAD OF INSERT
AS
BEGIN
	INSERT INTO dbo.Categories(CategoryName, Alias, CategoryDescription, AllowEntry, ParentCategoryId, IsWarrantyApplicable, IsUsed)
	SELECT CategoryName, dbo.GetAlias(CategoryName), CategoryDescription, AllowEntry, ParentCategoryId, IsWarrantyApplicable, IsUsed FROM inserted;
	
	PRINT 'If you have updated this table, don''t forget to update the trigger ''UpdateAliasTrigger''';
END;

GO

CREATE UNIQUE INDEX Categories_CategoryName_Uix
ON dbo.Categories(CategoryName);

GO

CREATE UNIQUE INDEX Categories_Alias_Uix
ON dbo.Categories(Alias);

GO




CREATE TABLE dbo.Users
(
	EmailAddress		national character varying(128) NOT NULL CONSTRAINT Users_PK PRIMARY KEY,
	Name				national character varying(128) NOT NULL,
	NickName			national character varying(32) NOT NULL CONSTRAINT Users_NickName_DF DEFAULT(''),
	Salt				character varying(128) NOT NULL CONSTRAINT Users_Salt_UNQ UNIQUE,
	Password			character varying(MAX) NOT NULL,
	RegisteredOn		datetime NOT NULL CONSTRAINT Users_RegisteredOn_DF DEFAULT(GETUTCDATE()),
	ConfirmedOn			datetime NULL,
	IsConfirmed			bit NOT NULL,
	LastActive			datetime NOT NULL CONSTRAINT Users_LastActive_DF DEFAULT(GETUTCDATE()),
	IsLockedOut			bit NOT NULL,
	LastLockOutDate		datetime NULL,
	LockOutDates		XML NULL,
	TotalLockOuts		integer,
	IsBanned			bit NOT NULL
);

GO

CREATE PROCEDURE dbo.CreateUser
	@EmailAddress		national character varying(128),
	@Name				national character varying(128),
	@NickName			national character varying(32),
	@Salt				character varying(128),
	@Password			character varying(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	INSERT INTO dbo.Users(EmailAddress, Name, NickName, Salt, Password, IsConfirmed, IsLockedOut, TotalLockOuts, IsBanned)
	SELECT @EmailAddress, @Name, @NickName, @Salt, @Password, 0, 0, 0, 0;
END;

GO

CREATE FUNCTION dbo.IsMobilePreviouslyRegistered
(
	@CellNumber character(10)
)
RETURNS bit
AS
BEGIN
	DECLARE @RetVal bit=0;

	IF EXISTS(SELECT TOP 1 1 FROM dbo.Profiles WHERE Status=1 AND CellNumber=@CellNumber AND Status=1)
	BEGIN
		SET @RetVal=1;
	END;
	
	RETURN @RetVal;
END;

GO

CREATE TABLE dbo.Profiles
(
	ProfileId				integer NOT NULL IDENTITY CONSTRAINT Profiles_PK PRIMARY KEY,
	Status					bit NOT NULL,
	EmailAddress			national character varying(128) NOT NULL 						
							CONSTRAINT Profiles_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	ProtectProfilePicture	bit NOT NULL CONSTRAINT Profiles_ProtectProfilePicture_DF DEFAULT(0),
	Sex						national character varying(48) NOT NULL,
	Name					national character varying(128) NOT NULL,
	NickName				national character varying(32) NOT NULL CONSTRAINT Profiles_NickName_DF DEFAULT(''),
	CellNumber				character(10) NOT NULL,
							CONSTRAINT Profiles_CellNumber_CHK CHECK(dbo.IsMobilePreviouslyRegistered(CellNumber)=1),
	DisplayCellNumber		bit NOT NULL,
	ContactNumbers			national character varying(128) NOT NULL CONSTRAINT Profiles_ContactNumbers_DF DEFAULT(''),
	DisplayContactNumbers	bit NOT NULL,
	AddressLine1			national character varying(128) NOT NULL,
	AddressLine2			national character varying(128) NOT NULL,
	AddressLine3			national character varying(128) NOT NULL,
	City					national character varying(128) NOT NULL,
	CityId					smallint NULL CONSTRAINT Profiles_Cities_FK FOREIGN KEY REFERENCES dbo.Cities(CityId),
	EntryDT					datetime NOT NULL CONSTRAINT Profiles_EntryDT_DF DEFAULT(GETUTCDATE())
);

GO

CREATE PROCEDURE dbo.UpdateProfile
	@EmailAddress			national character varying(128),
	@ProtectProfilePicture	bit,
	@Sex					national character varying(48),
	@Name					national character varying(128),
	@NickName				national character varying(32),
	@CellNumber				character(10),
	@DisplayCellNumber		bit,
	@ContactNumbers			national character varying(128),
	@DisplayContactNumbers	bit,
	@AddressLine1			national character varying(128),
	@AddressLine2			national character varying(128),
	@AddressLine3			national character varying(128),
	@City					national character varying(128)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ErrorMessage national character varying(4000);
	DECLARE @ErrorSeverity int;
	DECLARE @ErrorState int;
	DECLARE @Changed bit=0;

	BEGIN TRANSACTION;
	BEGIN TRY
		IF NOT EXISTS
		(
			SELECT TOP 1 1 FROM dbo.Profiles
			WHERE EmailAddress=@EmailAddress
			AND Status = 1
			AND ProtectProfilePicture=@ProtectProfilePicture
			AND Sex=@Sex
			AND Name=@Name
			AND NickName=@NickName
			AND CellNumber=@CellNumber
			AND DisplayCellNumber=@DisplayCellNumber
			AND ContactNumbers=@ContactNumbers
			AND DisplayContactNumbers=@DisplayContactNumbers
			AND AddressLine1=@AddressLine1
			AND AddressLine2=@AddressLine2
			AND AddressLine3=@AddressLine3
			AND City=@City
		)
		BEGIN
			SET @Changed=1;
		END;
		
		IF @Changed=1
		BEGIN
			UPDATE dbo.Profiles
			SET Status=0
			WHERE EmailAddress=@EmailAddress;
			
			INSERT INTO dbo.Profiles(EmailAddress, ProtectProfilePicture, Sex, Name, NickName, CellNumber, DisplayCellNumber, ContactNumbers, DisplayContactNumbers, AddressLine1, AddressLine2, AddressLine3, City, CityId, Status)
			SELECT @EmailAddress, @ProtectProfilePicture, @Sex, @Name, @NickName, @CellNumber, @DisplayCellNumber, @ContactNumbers, @DisplayContactNumbers, @AddressLine1, @AddressLine2, @AddressLine3, @City, dbo.GetCityId(@City), 1;

		END;		
			
		IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
		RETURN 1;
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
		
		SELECT @ErrorMessage = ERROR_MESSAGE()
		SELECT @ErrorSeverity = ERROR_SEVERITY()
		SELECT @ErrorState = ERROR_STATE()
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)		
	END CATCH;
	RETURN 0;
END;

GO

CREATE FUNCTION dbo.GetUserDisplayName
(
	@EmailAddress national character varying(128)
)
RETURNS national character varying(128)
AS
BEGIN
	DECLARE @DisplayName national character varying(128);
	
	IF EXISTS(SELECT TOP 1 1 FROM dbo.Profiles WHERE EmailAddress=@EmailAddress AND Status=1)
	BEGIN
		SET @DisplayName = 
		(
			SELECT NickName FROM dbo.Profiles
			WHERE EmailAddress=@EmailAddress
			AND Status=1
		);	
	END;
	ELSE
	BEGIN
		SET @DisplayName = 
		(
			SELECT NickName FROM dbo.Users
			WHERE EmailAddress=@EmailAddress			
		);	
	END;

	RETURN @DisplayName;
END;

GO

CREATE TABLE dbo.ProfileRatings
(
	ProfileRatingId			integer NOT NULL CONSTRAINT ProfileRatings_PK PRIMARY KEY,
	EmailAddress			national character varying(128) NOT NULL 						
							CONSTRAINT ProfileRatings_EmailAddress_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	RatedOn					datetime NOT NULL CONSTRAINT ProfileRatings_RatedOn_DF DEFAULT(GETUTCDATE()),
	RatedBy					national character varying(128) NOT NULL 						
							CONSTRAINT ProfileRatings_Rated_By_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	Rating					tinyint NOT NULL CHECK(Rating > 0 AND Rating <=100),
	Comment					national character varying(128) NOT NULL,
	Status					bit NOT NULL CONSTRAINT ProfileRatings_Status_DF DEFAULT(1)
);

GO

--This table is used for historical purpose only.
--The picture will be saved on application folder as emailaddress.jpg
CREATE TABLE dbo.ProfilePictures
(
	PictureId				integer NOT NULL IDENTITY CONSTRAINT ProfilePictures_PK PRIMARY KEY,
	EntryDT					datetime NOT NULL CONSTRAINT ProfilePictures_EntryDT_DF DEFAULT(GETUTCDATE()),
	EmailAddress			national character varying(128) NOT NULL 						
							CONSTRAINT ProfilePictures_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	Extension				character(4) NOT NULL,
	Picture					varbinary(MAX)
);

GO

CREATE TABLE dbo.ItemConditions
(
	ItemConditionId			smallint NOT NULL IDENTITY CONSTRAINT ItemConditions_PK PRIMARY KEY,
	ItemCondItionName		character varying(48) NOT NULL
);

GO

CREATE UNIQUE INDEX ItemConditions_ItemConditionName_Uix
ON dbo.ItemConditions(ItemCondItionName);

GO


CREATE TABLE dbo.FieldTypes
(
	FieldTypeName	national character varying(48) NOT NULL CONSTRAINT FieldTypes_PK PRIMARY KEY
);

GO

CREATE TABLE dbo.CustomFields
(
	CustomFieldId		integer NOT NULL CONSTRAINT CustomFields_PK PRIMARY KEY IDENTITY,
	CategoryId			integer NULL CONSTRAINT CustomFields_Categories_FK FOREIGN KEY REFERENCES dbo.Categories(CategoryId),
	FieldOrder			smallint NOT NULL CONSTRAINT CustomFields_FieldOrder_DF DEFAULT(0),
	FieldStatus			bit NOT NULL CONSTRAINT CustomFields_Status_DF DEFAULT(0),
	FieldLabel			national character varying(128) NOT NULL,
	Note				national character varying(4000) NOT NULL,
	FieldTypeName		national character varying(48) NOT NULL CONSTRAINT CustomFields_FieldTypes_FK FOREIGN KEY REFERENCES dbo.FieldTypes(FieldTypeName),
	MaxLength			smallint NOT NULL,
	MaxValue			character varying(48),
	List				national character varying(4000),
	InitialValue		national character varying(4000) CONSTRAINT CustomFields_InitialValue_DF DEFAULT(''),
	IsExpression		bit NOT NULL CONSTRAINT CustomFields_IsExpression_DF DEFAULT(0),
	CssClass			national character varying(4000),
	FieldRequired		bit NOT NULL CONSTRAINT CustomFields_FieldRequired_DF DEFAULT(0),
	RegularExpression	national character varying(128) NOT NULL CONSTRAINT CustomFields_RegularExpress_DF DEFAULT('')
);

GO

CREATE INDEX CustomFields_ScopeCode_FieldOrder_INX
ON dbo.CustomFields(FieldOrder);

GO
CREATE INDEX CustomFields_ScopeCode_FieldOrder_CustomFieldId_INX
ON dbo.CustomFields(FieldOrder, CustomFieldId);

GO

CREATE TABLE dbo.ItemStatuses
(
	ItemStatusId	tinyint NOT NULL CONSTRAINT ItemStatuses_PK PRIMARY KEY,
	ItemStatusName	character varying(128)
);

GO

INSERT INTO dbo.ItemStatuses(ItemStatusId, ItemStatusName)
SELECT '0', 'Normal' UNION ALL
SELECT '1', 'Sold Out' UNION ALL
SELECT '2', 'Withdrawn';

GO


CREATE TABLE dbo.Items
(
	ItemId			bigint NOT NULL IDENTITY CONSTRAINT Items_PK PRIMARY KEY,
	CityId			smallint NULL CONSTRAINT Items_Cities_FK FOREIGN KEY REFERENCES dbo.Cities(CityId),
	CategoryId		integer NOT NULL CONSTRAINT Items_Categories_FK FOREIGN KEY REFERENCES dbo.Categories(CategoryId),
	EmailAddress	national character varying(128) NOT NULL 						
					CONSTRAINT Items_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	AddedBy			national character varying(32) NOT NULL,
	Title			national character varying(46) NOT NULL,
	Alias			national character varying(48) NOT NULL,
	Price			money NOT NULL,
	PriceType		smallint NOT NULL CONSTRAINT Items_CanNegotiate_DF DEFAULT(0)
					CONSTRAINT Items_PriceType_CHK 
					CHECK
					(
						PriceType IN
						(
							-1, --Price non negotiable
							 0, --Price not applicable
							 1 --Price is negotiable
						)
					),
	Details			national character varying(4000) NOT NULL,
	AddedOn			datetime NOT NULL CONSTRAINT Items_AddedOn_DF DEFAULT(GETUTCDATE()),
	WasRenewed		bit NOT NULL CONSTRAINT Items_WasRenewed_DF DEFAULT(0),
	LastEditedOn	datetime NOT NULL,
	TotalEdits		integer,
	ExpiresOn		datetime NOT NULL,
	DaysLeft		AS DATEDIFF(dd, GetUTCDate(), ExpiresOn),
	ItemStatusId	tinyint NOT NULL CONSTRAINT Items_ItemStatuses_FK FOREIGN KEY REFERENCES dbo.ItemStatuses(ItemStatusId)
					CONSTRAINT Items_ItemStatusId_DF DEFAULT(0),
	LastViewedOn	datetime NOT NULL CONSTRAINT Items_LastViewedOn DEFAULT(GETUTCDATE()),
	TotalViews		integer NOT NULL CONSTRAINT Items_TotalViews_DF DEFAULT(0)
);

GO

CREATE INDEX Items_WasRenewed_INX
ON dbo.Items(WasRenewed);

GO

CREATE INDEX Items_Price_INX
ON dbo.Items(Price);

GO

CREATE INDEX Items_ItemStatusId_INX
ON dbo.Items(ItemStatusId);

GO

CREATE Index Items_TotalViews_INX
ON dbo.Items(TotalViews);

GO

CREATE Index Items_AddedOn_INX
ON dbo.Items(AddedOn);

GO

CREATE Index Items_CityId_INX
ON dbo.Items(CityId);

GO


CREATE FUNCTION dbo.GetParentCategoryAliasByItemId
(
	@ItemId bigint
)
RETURNS national character varying(128)
AS
BEGIN
	RETURN
	(
		SELECT Alias
		FROM Categories
		WHERE CategoryId=
		(
			SELECT ParentCategoryId
			FROM Categories
			WHERE CategoryId =
			(
				SELECT CategoryId
				FROM dbo.Items
				WHERE ItemId=@ItemId
			)
		)
	);
END;

GO


CREATE FUNCTION dbo.GetCategoryAliasByItemId
(
	@ItemId bigint
)
RETURNS national character varying(128)
AS
BEGIN
	RETURN
	(
		SELECT Alias
		FROM Categories
		WHERE CategoryId=
		(
			SELECT CategoryId
			FROM dbo.Items
			WHERE ItemId=@ItemId
		)
	);
END;

GO

GO

CREATE FUNCTION dbo.IsPopular
(
	@ItemId	bigint
)
RETURNS bit
AS
BEGIN
	IF EXISTS
	(
		SELECT TOP 1 1 FROM
		(
			SELECT TOP 20 ItemId FROM Items
			WHERE ItemStatusId = 0
			AND DaysLeft >=0
			ORDER BY TotalViews DESC
		) Derived
		WHERE ItemId=@ItemId
	)
	BEGIN
		RETURN 1;
	END;

	RETURN 0;	
END;

GO


CREATE FUNCTION dbo.IsNew
(
	@ItemId	bigint
)
RETURNS bit
AS
BEGIN
	IF EXISTS
	(
		SELECT TOP 1 1 FROM
		(
			SELECT TOP 20 ItemId FROM Items
			WHERE ItemStatusId = 0
			AND DaysLeft >=0
			ORDER BY AddedOn DESC
		) Derived
		WHERE ItemId=@ItemId
	)
	BEGIN
		RETURN 1;
	END;

	RETURN 0;	
END;

GO

CREATE VIEW [dbo].[ItemView]
AS
SELECT     dbo.Items.ItemId, dbo.Items.CategoryId, dbo.Items.CityId, dbo.Items.EmailAddress, dbo.GetUserDisplayName(dbo.Items.EmailAddress) AS DisplayName, dbo.Items.AddedBy, 
                      dbo.Items.Title, dbo.Items.Alias, dbo.Items.Price, dbo.Items.PriceType, dbo.Items.Details, dbo.Items.AddedOn, dbo.Items.LastEditedOn, dbo.Items.TotalEdits, dbo.Items.ExpiresOn, 
                      dbo.Items.DaysLeft, dbo.Items.ItemStatusId, dbo.Items.LastViewedOn, dbo.Items.TotalViews, dbo.Categories.CategoryName, dbo.Categories.CategoryDescription, dbo.Cities.CityName, 
                      dbo.ItemStatuses.ItemStatusName, dbo.Users.Salt,                      
                      CASE 
						WHEN dbo.Items.DaysLeft < 0 THEN 'Expired'
						WHEN dbo.Items.ItemStatusId = 1 THEN 'Sold'
						WHEN dbo.Items.ItemStatusId = 2 THEN 'Withdrawn'
						ELSE 'Normal'
                      END AS ItemStatus, 
                      dbo.IsPopular(ItemId) AS IsPopular,
                      dbo.IsNew(ItemId) AS IsNew                      
FROM         dbo.Items INNER JOIN
                      dbo.Categories ON dbo.Items.CategoryId = dbo.Categories.CategoryId INNER JOIN
                      dbo.ItemStatuses ON dbo.Items.ItemStatusId = dbo.ItemStatuses.ItemStatusId INNER JOIN
                      dbo.Users ON dbo.Items.EmailAddress = dbo.Users.EmailAddress LEFT OUTER JOIN
                      dbo.Cities ON dbo.Items.CityId = dbo.Cities.CityId;

GO

CREATE TABLE dbo.ItemImages
(
	ImageId				bigint NOT NULL IDENTITY CONSTRAINT ItemImages_PK PRIMARY KEY,
	ItemId				bigint NOT NULL CONSTRAINT ItemImages_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	FilePath			national character varying(94) NOT NULL CONSTRAINT ItemImages_FilePath_UNQ UNIQUE,
	AddedOn				datetime NOT NULL CONSTRAINT ItemImages_AddedOn_DF DEFAULT(GETUTCDATE()),
	IsDeleted			bit NOT NULL CONSTRAINT ItemImages_IsDeleted_DF DEFAULT(0),
	DeletedOn			datetime NULL
);

GO

CREATE TABLE dbo.FavoriteItems
(
	FavoriteItemId			bigint NOT NULL IDENTITY CONSTRAINT FavoriteItems_PK PRIMARY KEY,
	ItemId					bigint NOT NULL CONSTRAINT FavoriteItems_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	AddedOn					datetime NOT NULL CONSTRAINT FavoriteItems_AddedOn_DF DEFAULT(GETUTCDATE()),
	EmailAddress			national character varying(128) NOT NULL CONSTRAINT FavoriteItems_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	Status					bit NOT NULL CONSTRAINT FavoriteItems_Status_DF DEFAULT(1)
);

GO

CREATE TABLE dbo.ItemCustomFields
(
	ItemCustomFieldId			bigint NOT NULL CONSTRAINT ItemCustomFields_PK PRIMARY KEY IDENTITY,
	ItemId						bigint NOT NULL CONSTRAINT ItemCustomFields_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	CustomFieldId				integer NOT NULL CONSTRAINT ItemCustomFields_CustomFields_FK FOREIGN KEY REFERENCES dbo.CustomFields(CustomFieldId),
	FieldValue					national character varying(MAX),
	EntryDT						datetime NOT NULL CONSTRAINT ItemCustomFields_EntryDT_DF DEFAULT(GETUTCDATE()),
	Status						bit NOT NULL CONSTRAINT ItemCustomFields_Status_DF DEFAULT(1)
);

GO


CREATE TYPE CustomFieldValuesType AS TABLE
(
	CustomFieldId				integer UNIQUE,
	FieldValue					national character varying(MAX)
);

GO

CREATE VIEW dbo.ItemCustomFieldView
AS
SELECT     dbo.ItemCustomFields.ItemId, dbo.ItemCustomFields.CustomFieldId, dbo.CustomFields.FieldLabel, dbo.ItemCustomFields.FieldValue, dbo.ItemCustomFields.EntryDT, dbo.ItemCustomFields.Status, 
                      dbo.CustomFields.FieldTypeName, dbo.CustomFields.MaxLength, dbo.CustomFields.MaxValue, dbo.CustomFields.List, dbo.CustomFields.InitialValue, dbo.CustomFields.IsExpression, 
                      dbo.CustomFields.CssClass, dbo.CustomFields.FieldRequired, dbo.CustomFields.RegularExpression
FROM         dbo.ItemCustomFields INNER JOIN
                      dbo.CustomFields ON dbo.ItemCustomFields.CustomFieldId = dbo.CustomFields.CustomFieldId;

GO

CREATE TABLE dbo.ItemOtherFields
(
	ItemCustomFieldId			bigint NOT NULL CONSTRAINT ItemOtherFields_PK PRIMARY KEY IDENTITY,
	ItemId						bigint NOT NULL CONSTRAINT ItemOtherFields_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	FieldName					national character varying(128),
	FieldValue					national character varying(128),
	EntryDT						datetime NOT NULL CONSTRAINT ItemOtherFields_EntryDT_DF DEFAULT(GETUTCDATE()),
	Status						bit NOT NULL CONSTRAINT ItemOtherFields_Status_DF DEFAULT(1)
);

GO

CREATE TYPE OtherFieldsType AS TABLE
(
	FieldName					national character varying(128),
	FieldValue					national character varying(128)
);

GO

CREATE TABLE dbo.ItemLikes
(
	LikeId					bigint NOT NULL IDENTITY CONSTRAINT ItemsLikes_PK PRIMARY KEY,
	ItemId					bigint NOT NULL CONSTRAINT ItemLikes_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	LikedOn					datetime NOT NULL CONSTRAINT ItemLikes_LikedOn_DF DEFAULT(GETUTCDATE()),
	EmailAddress			national character varying(128) NOT NULL 						
							CONSTRAINT ItemLikes_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	Liker					national character varying(32) NOT NULL,
	Unliked					bit NOT NULL CONSTRAINT ItemLikes_Unliked_DF DEFAULT(0),
	UnlikedDate				datetime NULL
);

GO

CREATE TABLE dbo.Comments
(
	CommentId				integer NOT NULL IDENTITY CONSTRAINT Comments_PK PRIMARY KEY,
	ItemId					bigint NOT NULL CONSTRAINT Comments_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	CommentedOn				datetime NOT NULL CONSTRAINT Comments_CommentedOn_DF DEFAULT(GETUTCDATE()),
	EmailAddress			national character varying(128) NOT NULL 						
							CONSTRAINT Comments_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	Commentor				national character varying(32) NOT NULL,
	Comment					national character varying(128) NOT NULL
);

GO

CREATE TABLE dbo.Messages
(
	MessageId				bigint NOT NULL IDENTITY CONSTRAINT Messages_PK PRIMARY KEY,
	ItemId					bigint NULL CONSTRAINT Messages_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
	SentBy					national character varying(128) NOT NULL CONSTRAINT Messages_SentBy_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	SentTo					national character varying(128) NOT NULL CONSTRAINT Messages_SentTo_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	SentOn					datetime NOT NULL CONSTRAINT Messages_SentOn_DF DEFAULT(GETUTCDATE()),
	Message					national character varying(500) NOT NULL
);

GO

CREATE PROCEDURE dbo.CreateEmail
	@Domain					national character varying(128),
	@PKValue				national character varying(128),	
	@EmailAddress			national character varying(128),
	@Subject				national character varying(128),
	@Message				national character varying(MAX),
	@SentBy					national character varying(128)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ApplicationId integer = (SELECT ApplicationId FROM Messaging.dbo.Applications WHERE ApplicationName='MixNP');
	
	INSERT INTO Messaging.dbo.OutgoingEmails(ApplicationId, Domain, PKValue, EmailAddress, Subject, Message, SentBy)
	SELECT @ApplicationId, @Domain, @PKValue, @EmailAddress, @Subject, @Message, @SentBy;
END;

GO

CREATE PROCEDURE dbo.CreateText
	@Domain					national character varying(128),
	@CellNumber			national character varying(128),
	@Message				national character varying(MAX)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @ApplicationId integer = (SELECT ApplicationId FROM Messaging.dbo.Applications WHERE ApplicationName='MixNP');
	
	INSERT INTO Messaging.dbo.OutgoingTexts(ApplicationId, Domain, CellNumber, Message)
	SELECT @ApplicationId, @Domain, @CellNumber, @Message;
END;

GO

CREATE FUNCTION dbo.GetTotalItems
(
	@CategoryId integer
)
RETURNS integer
AS
BEGIN
	RETURN 0;
END;

GO

CREATE FUNCTION dbo.GetParentCategoryName
(
	@CategoryId integer
)
RETURNS character varying(128)
AS
BEGIN
	RETURN
	COALESCE
	(
		(
			SELECT CategoryName 
			FROM dbo.Categories
			WHERE CategoryId=
			(
				SELECT ParentCategoryId 
				FROM dbo.Categories
				WHERE CategoryId=@CategoryId
			)
		), 
	'');
END;

GO


CREATE PROCEDURE dbo.InsertItem
	@CategoryId			integer,
	@EmailAddress		national character varying(128),
	@AddedBy			national character varying(32),
	@Title				national character varying(32),
	@Price				money,
	@PriceType			smallint,
	@Details			national character varying(4000),
	@CustomFieldsTable	CustomFieldValuesType READONLY,
	@OtherFieldsTable	OtherFieldsType READONLY,
	@ItemId				bigint OUTPUT
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Timestamp datetime = GETDATE();
	DECLARE @ErrorMessage national character varying(4000);
	DECLARE @ErrorSeverity integer;
	DECLARE @ErrorState integer;
	
	DECLARE @RetVal tinyint=1;

	BEGIN TRY
		BEGIN TRANSACTION;
		
		INSERT INTO dbo.Items(CategoryId, EmailAddress, AddedBy, Title, Alias, Price, PriceType, Details, AddedOn, LastEditedOn, TotalEdits, ExpiresOn, LastViewedOn, TotalViews) 
		SELECT @CategoryId, @EmailAddress, @AddedBy, @Title, dbo.GetAlias(@Title), @Price, @PriceType, @Details, GETUTCDATE(), GETUTCDATE(), 0, GETUTCDATE() + 60, GETUTCDATE(), 1;
					
		SET @ItemId = SCOPE_IDENTITY();

		INSERT INTO dbo.ItemCustomFields(ItemId, CustomFieldId, FieldValue)
		SELECT @ItemId, CustomFieldId, FieldValue
		FROM @CustomFieldsTable;

		INSERT INTO dbo.ItemOtherFields(ItemId, FieldName, FieldValue)
		SELECT @ItemId, FieldName, FieldValue
		FROM @OtherFieldsTable;
			
		IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		SET @RetVal = 0;
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		SELECT @ErrorMessage = ERROR_MESSAGE();
		SELECT @ErrorSeverity = ERROR_SEVERITY();
		SELECT @ErrorState = ERROR_STATE();
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH		

	
	RETURN @RetVal;
END;

GO


CREATE PROCEDURE dbo.UpdateItem
	@ItemId				bigint,
	@CategoryId			integer,
	@Title				national character varying(32),
	@Price				money,
	@PriceType			smallint,
	@Details			national character varying(4000),
	@CustomFieldsTable	CustomFieldValuesType READONLY,
	@OtherFieldsTable	OtherFieldsType READONLY
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @Timestamp datetime = GETDATE();
	DECLARE @ErrorMessage national character varying(4000);
	DECLARE @ErrorSeverity integer;
	DECLARE @ErrorState integer;
	
	DECLARE @RetVal tinyint=1;

	BEGIN TRY
		BEGIN TRANSACTION;
		
		UPDATE dbo.Items
		SET 
			CategoryId=@CategoryId,
			Title=@Title, 
			Price=@Price, 
			PriceType=@PriceType, 
			Details=@Details, 
			LastEditedOn = GETUTCDATE(), 
			TotalEdits = TotalEdits + 1
		WHERE ItemId=@ItemId;
		

		INSERT INTO dbo.ItemCustomFields(ItemId, CustomFieldId, FieldValue)
		SELECT @ItemId, CustomFieldId, FieldValue
		FROM @CustomFieldsTable
		WHERE CustomFieldId NOT IN
		(
			SELECT CustomFieldId
			FROM dbo.ItemCustomFields
			WHERE ItemId = @ItemId
		);
		
		UPDATE dbo.ItemCustomFields
		SET Status=0
		WHERE CustomFieldId NOT IN
		(
			SELECT CustomFieldId
			FROM @CustomFieldsTable
		)
		AND ItemId=@ItemId;
		
		UPDATE dbo.ItemCustomFields
		SET FieldValue=c.FieldValue
		FROM dbo.ItemCustomFields, @CustomFieldsTable c
		WHERE dbo.ItemCustomFields.CustomFieldId = c.CustomFieldId
		AND dbo.ItemCustomFields.ItemId=@ItemId;

		INSERT INTO dbo.ItemOtherFields(ItemId, FieldName, FieldValue)
		SELECT @ItemId, FieldName, FieldValue
		FROM @OtherFieldsTable
		WHERE FieldName NOT IN
		(
			SELECT FieldName
			FROM dbo.ItemOtherFields
			WHERE ItemId = @ItemId
		);
		
		UPDATE dbo.ItemOtherFields
		SET Status=0
		WHERE FieldName NOT IN
		(
			SELECT FieldName
			FROM @OtherFieldsTable
		)
		AND ItemId=@ItemId;

		UPDATE dbo.ItemOtherFields
		SET Status=1
		WHERE FieldName IN
		(
			SELECT FieldName
			FROM @OtherFieldsTable
		)
		AND ItemId=@ItemId;

		
		UPDATE dbo.ItemOtherFields
		SET FieldValue=o.FieldValue
		FROM dbo.ItemOtherFields, @OtherFieldsTable o
		WHERE dbo.ItemOtherFields.FieldName = o.FieldName
		AND dbo.ItemOtherFields.ItemId=@ItemId;
					
		IF @@TRANCOUNT > 0 COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		SET @RetVal = 0;
		IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

		SELECT @ErrorMessage = ERROR_MESSAGE();
		SELECT @ErrorSeverity = ERROR_SEVERITY();
		SELECT @ErrorState = ERROR_STATE();
		RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	END CATCH		

	
	RETURN @RetVal;
END;

GO

CREATE TABLE dbo.AlertMe
(
	AlertMeId		bigint NOT NULL IDENTITY CONSTRAINT AlertMe_PK PRIMARY KEY,
	EmailAddress	national character varying(128) NOT NULL CONSTRAINT AlertMe_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	CategoryId		integer NOT NULL CONSTRAINT AlertMe_Categories_FK FOREIGN KEY REFERENCES dbo.Categories(CategoryId),
	CityId			smallint NULL CONSTRAINT AlertMe_Cities_FK FOREIGN KEY REFERENCES dbo.Cities(CityId),
	PriceFrom		money NOT NULL CONSTRAINT AlertMe_PriceFrom_DF DEFAULT(0),
	PriceTo			money NOT NULL CONSTRAINT AlertMe_PriceTo_DF DEFAULT(0)
);

GO

CREATE PROCEDURE PageItems 
	@PageNumber integer,
	@PageSize	integer, 
	@Filter		national character varying(MAX),
	@Order		national character varying(MAX),
	@ItemCount	int OUTPUT
AS
BEGIN
	DECLARE @Sql			national character varying(4000) = '';
	DECLARE @RowsToSkip		national character varying(20) = '';
	DECLARE @OrderBy		national character varying(4000) = '';

	If(@PageNumber < 0) 
	BEGIN
		SET @PageNumber = 1;
	END;
	
	SET @RowsToSkip = CAST(((@PageNumber - 1) * @PageSize) AS character varying(20))
	
	if(LEN(LTRIM(RTRIM(@Order))) > 0)
	BEGIN
		SET @OrderBy = ' Order By ' + @Order;
	END;

	SET @Sql = N'SELECT @ItemCount = Count(ItemId) FROM dbo.Items ' + @Filter
	exec sp_executesql @SQL, N'@ItemCount int out', @ItemCount out

	SET @Sql = 'SELECT ItemId FROM dbo.Items WHERE ItemId IN 
	(SELECT TOP ' + CAST(@PageSize AS varchar) + ' ItemId FROM dbo.Items ' + @Filter + 
				 ' AND  ItemId NOT IN 
				 (SELECT TOP ' + @RowsToSkip + ' ItemId FROM dbo.Items ' + @Filter + @OrderBy + ') ' 
	   + @OrderBy + ') ' + @OrderBy
	Print @Sql 
	EXEC sp_executesql @Sql;
END;

GO

CREATE PROCEDURE GetPagedItems
(
    @PageNumber		integer,
    @NameLike		national character varying(128),
    @CategoryId		integer,
    @PriceFrom		money,
    @PriceTo		money,
    @CityId			smallint,
    @Order			smallint,
	@ItemCount		int OUTPUT
)
AS
BEGIN
	DECLARE @Price bit=0;
	DECLARE @TitlePattern national character varying(130)= '''%' + @NameLike + '%''';
	DECLARE @WHERE national character varying(MAX);
	DECLARE @OrderBy national character varying(MAX);
    DECLARE @PageSize integer = 20;
	DECLARE @Count integer;
	
	IF(@PriceFrom > 0)
	BEGIN
		IF(@PriceTo > @PriceFrom)
		BEGIN
			SET @Price = 1;
		END;
	END;
	
		
	SET @WHERE = 'WHERE Title LIKE ' + @TitlePattern;

	IF (@Price = 1)
	BEGIN
		SET @WHERE = @WHERE + ' AND Price BETWEEN ' + CAST(@PriceFrom AS character varying) + ' AND ' + CAST(@PriceTo AS character varying);
	END;

	IF(@CityId > 0)
	BEGIN
		SET @WHERE = @WHERE + ' AND CityId =' + CAST(@CityId AS character varying);
	END;

	IF(@CategoryId > 0)
	BEGIN
		SET @WHERE = @WHERE + ' AND CategoryId=' + CAST(@CategoryId AS character varying);
	END;	

	
	IF(@Order = 0)
	BEGIN
		SET @OrderBy = ' TotalViews DESC, AddedOn DESC';
	END;

	
	IF(@Order = 1)
	BEGIN
		SET @OrderBy = ' Price ASC, TotalViews DESC, AddedOn DESC';
	END;
		
	IF(@Order = 2)
	BEGIN
		SET @OrderBy = ' Price DESC, TotalViews DESC, AddedOn DESC';
	END;
	
	IF(@Order = 3)
	BEGIN
		SET @OrderBy = @OrderBy + ' AddedOn DESC, TotalViews DESC';
	END;
	
	EXECUTE dbo.PageItems @PageNumber, @PageSize, @WHERE, @OrderBy, @ItemCount OUTPUT;
END
GO

CREATE VIEW dbo.ItemAliasView
AS
SELECT
	Items.ItemId,
	Items.Alias AS ItemAlias,
	Categories.Alias AS CategoryAlias,
	ParentCategories.Alias AS ParentCategoryAlias
FROM Items, Categories, Categories ParentCategories
WHERE Items.CategoryId = Categories.CategoryId
AND Categories.ParentCategoryId = ParentCategories.CategoryId

GO

CREATE VIEW dbo.CategoryAliasView
AS
SELECT
	Categories.CategoryId,
	Categories.Alias AS CategoryAlias,
	ParentCategories.Alias AS ParentCategoryAlias
FROM Categories, Categories ParentCategories
WHERE Categories.ParentCategoryId = ParentCategories.CategoryId

GO

CREATE TABLE dbo.Watchlist
(
	Id					bigint NOT NULL IDENTITY CONSTRAINT Watchlist_PK PRIMARY KEY,
	EmailAddress		national character varying(128) NOT NULL CONSTRAINT Watchlist_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	ItemId				bigint NOT NULL CONSTRAINT Watchlist_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
);

GO

CREATE INDEX Watchlist_EmailAddress_INX
ON dbo.Watchlist(EmailAddress);

GO

CREATE INDEX Watchlist_ItemId_INX
ON dbo.Watchlist(ItemId);

GO

CREATE UNIQUE INDEX Watchlist_EmailAddress_ItemId_UIX
ON dbo.Watchlist(EmailAddress, ItemId);

GO


CREATE TABLE dbo.Wishlist
(
	Id					bigint NOT NULL IDENTITY CONSTRAINT Wishlist_PK PRIMARY KEY,
	EmailAddress		national character varying(128) NOT NULL CONSTRAINT Wishlist_Users_FK FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	ItemId				bigint NOT NULL CONSTRAINT Wishlist_Items_FK FOREIGN KEY REFERENCES dbo.Items(ItemId),
);

GO

CREATE INDEX Wishlist_EmailAddress_INX
ON dbo.Wishlist(EmailAddress);

GO

CREATE INDEX Wishlist_ItemId_INX
ON dbo.Wishlist(ItemId);

GO


CREATE UNIQUE INDEX Wishlist_EmailAddress_ItemId_UIX
ON dbo.Wishlist(EmailAddress, ItemId);

GO


CREATE View NewIdView
AS
SELECT NEWID() AS new_id

GO


CREATE FUNCTION GetRandomImagePath
(
	@ItemId bigint
)
RETURNS national character varying(1000)
AS
BEGIN
	RETURN
	(
		SELECT TOP 1 FilePath FROM ItemImages
		WHERE ItemId=@ItemId
		ORDER BY (SELECT new_id FROM NewIdView)
	);
END;



GO



CREATE TABLE dbo.PasswordResetRequests
(
	RequestId			uniqueidentifier NOT NULL CONSTRAINT PasswordResetRequests_PK PRIMARY KEY
						CONSTRAINT PasswordResetRequests_PK_DF DEFAULT(newsequentialid()),
	EmailAddress		national character varying(128) NOT NULL CONSTRAINT PasswordResetRequests_Users_FK 
						FOREIGN KEY REFERENCES dbo.Users(EmailAddress),
	RecoveryMethod		char(1) NOT NULL 
						CONSTRAINT PasswordResetRequests_RecoveryMethod_CHK	CHECK(RecoveryMethod IN('E', 'T')),
	RecoveryAddress		national character varying(128) NOT NULL,
	RequestedOn			datetime NOT NULL CONSTRAINT PasswordResetRequests_RequestedOn_DF DEFAULT(GETUTCDATE()),
	RecoveryToken		character varying(128) NOT NULL,
	RecoveredOn			datetime NULL,
	Status				AS
						(CASE WHEN RequestedOn + 1 >= GETUTCDATE() AND RecoveredOn IS NULL THEN 'Y' ELSE 'N' END)
);



GO



CREATE INDEX PasswordResetRequests_EmailAddress_INX
ON dbo.PasswordResetRequests(EmailAddress);

GO

CREATE INDEX PasswordResetRequests_RecoveryMethod_INX
ON dbo.PasswordResetRequests(RecoveryMethod);

GO
CREATE INDEX PasswordResetRequests_RecoveryToken_INX
ON dbo.PasswordResetRequests(RecoveryToken);

GO

USE [MixNP]
GO

/****** Object:  StoredProcedure [dbo].[Search]    Script Date: 05/29/2013 23:59:19 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Search]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Search]
GO

USE [MixNP]
GO

/****** Object:  StoredProcedure [dbo].[Search]    Script Date: 05/29/2013 23:59:19 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[Search]
(
    @PageNumber		integer,
    @Query			national character varying(128),
    @CategoryId		integer,
    @PriceFrom		money,
    @PriceTo		money,
    @CityId			smallint,
    @Order			smallint,
	@ItemCount		int OUTPUT
)
AS
BEGIN
	DECLARE @Price bit=0;
	DECLARE @TitlePattern national character varying(130)= '''%' + @Query + '%''';
	DECLARE @WHERE national character varying(MAX);
	DECLARE @OrderBy national character varying(MAX);
    DECLARE @PageSize integer = 20;
	DECLARE @Count integer;
	
	IF(@PriceFrom > 0)
	BEGIN
		IF(@PriceTo > @PriceFrom)
		BEGIN
			SET @Price = 1;
		END;
	END;
	
		
	SET @WHERE = 'WHERE (Title LIKE ' + @TitlePattern + ' OR Details LIKE ' + @TitlePattern + ')';

	IF (@Price = 1)
	BEGIN
		SET @WHERE = @WHERE + ' AND Price BETWEEN ' + CAST(@PriceFrom AS character varying) + ' AND ' + CAST(@PriceTo AS character varying);
	END;

	IF(@CityId > 0)
	BEGIN
		SET @WHERE = @WHERE + ' AND CityId =' + CAST(@CityId AS character varying);
	END;

	IF(@CategoryId > 0)
	BEGIN
		SET @WHERE = @WHERE + ' AND CategoryId=' + CAST(@CategoryId AS character varying);
	END;	

	IF(@Order = 0)
	BEGIN
		SET @OrderBy = 'TotalViews DESC, AddedOn DESC';
	END;

	
	IF(@Order = 1)
	BEGIN
		SET @OrderBy = ' Price ASC, TotalViews DESC, AddedOn DESC';
	END;
		
	IF(@Order = 2)
	BEGIN
		SET @OrderBy = ' Price DESC, TotalViews DESC, AddedOn DESC';
	END;
	
	IF(@Order = 3)
	BEGIN
		SET @OrderBy = @OrderBy + ' AddedOn DESC, TotalViews DESC';
	END;
	
	EXECUTE dbo.PageItems @PageNumber, @PageSize, @WHERE, @OrderBy, @ItemCount OUTPUT;
END

GO



CREATE FUNCTION dbo.CountTotalAdsFunction
(
	@EmailAddress national character varying(128)
)
RETURNS integer
AS
BEGIN
	RETURN
	(
		SELECT COUNT(*) 
		FROM ItemView
		WHERE EmailAddress=@EmailAddress
	);
END;

GO

CREATE FUNCTION dbo.CountExpiredAdsFunction
(
	@EmailAddress national character varying(128)
)
RETURNS integer
AS
BEGIN
	RETURN
	(
		SELECT COUNT(*) 
		FROM ItemView
		WHERE EmailAddress=@EmailAddress
		AND ItemStatus='Expired'
	);
END;

GO

CREATE FUNCTION dbo.CountWithdrawnAdsFunction
(
	@EmailAddress national character varying(128)
)
RETURNS integer
AS
BEGIN
	RETURN
	(
		SELECT COUNT(*) 
		FROM ItemView
		WHERE EmailAddress=@EmailAddress
		AND ItemStatus='Withdrawn'
	);
END;
GO

CREATE FUNCTION dbo.CountSoldAdsFunction
(
	@EmailAddress national character varying(128)
)
RETURNS integer
AS
BEGIN
	RETURN
	(
		SELECT COUNT(*) 
		FROM ItemView
		WHERE EmailAddress=@EmailAddress
		AND ItemStatus='Sold'
	);
END;

GO


CREATE FUNCTION dbo.CountActiveAdsFunction
(
	@EmailAddress national character varying(128)
)
RETURNS integer
AS
BEGIN
	RETURN
	(
		SELECT COUNT(*) 
		FROM ItemView
		WHERE EmailAddress=@EmailAddress
		AND ItemStatus='Normal'
		AND ItemStatusName='Normal'
	);
END;

GO

CREATE VIEW dbo.UserProfileView
AS
SELECT     dbo.Users.EmailAddress, dbo.Users.Salt, dbo.Users.RegisteredOn, dbo.Users.ConfirmedOn, dbo.Users.IsConfirmed, dbo.Users.LastActive, dbo.Users.IsLockedOut, dbo.Users.LastLockOutDate, 
                      dbo.Users.LockOutDates, dbo.Users.TotalLockOuts, dbo.Users.IsBanned, dbo.Profiles.ProfileId, dbo.Profiles.Status, dbo.Profiles.ProtectProfilePicture, dbo.Profiles.Sex, dbo.Profiles.Name, 
                      dbo.Profiles.NickName, dbo.Profiles.CellNumber, dbo.Profiles.DisplayCellNumber, dbo.Profiles.ContactNumbers, dbo.Profiles.DisplayContactNumbers, dbo.Profiles.AddressLine1, 
                      dbo.Profiles.AddressLine2, dbo.Profiles.AddressLine3, dbo.Profiles.City, dbo.Profiles.CityId, dbo.CountTotalAdsFunction(dbo.Users.EmailAddress) AS TotalAds, 
                      dbo.CountExpiredAdsFunction(dbo.Users.EmailAddress) AS ExpiredAds, dbo.CountWithdrawnAdsFunction(dbo.Users.EmailAddress) AS WithdrawnAds, 
                      dbo.CountSoldAdsFunction(dbo.Users.EmailAddress) AS Sold, dbo.CountActiveAdsFunction(dbo.Users.EmailAddress) AS ActiveAds, dbo.Profiles.EntryDT
FROM         dbo.Profiles INNER JOIN
                      dbo.Users ON dbo.Profiles.EmailAddress = dbo.Users.EmailAddress
WHERE     (dbo.Profiles.Status = 1);

GO
